﻿using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections.Specialized;
using System.Data;
using System.IO;

namespace TurtleEngine
{
	 /// <summary>
	 ///
	 /// </summary>
	 public class SMOHelper
	 {
		  /// <summary>
		  ///
		  /// </summary>
		  public class TriggersHelper
		  {
				/// <summary>
				/// The triggers
				/// </summary>
				private static readonly DataTable triggers = new DataTable("Triggers");

				/// <summary>
				/// Gets the triggers.
				/// </summary>
				/// <value>
				/// The triggers.
				/// </value>
				public static DataTable Triggers
				{
					 get { return triggers; }
				}

				/// <summary>
				/// Gets the triggers.
				/// </summary>
				/// <param name="database">The database.</param>
				public static void GetTriggers(string database)
				{
					 var serverConnection = new ServerConnection(Utility.DBConnection);
					 var server = new Server(serverConnection);
					 Database db = server.Databases[database];

					 var so = new ScriptingOptions();
					 so.ChangeTracking = true;
					 so.ClusteredIndexes = true;
					 so.ExtendedProperties = true;

					 CreateTriggersSchema();
					 triggers.Clear();

					 foreach (Table table in db.Tables)
					 {
						  TriggerCollection triggerCollection = table.Triggers;
						  if (triggerCollection.Count > 0)
						  {
								foreach (Trigger trigger in triggerCollection)
								{
									 StringCollection script = trigger.Script(so);
									 var scriptArray = new string[script.Count];
									 script.CopyTo(scriptArray, 0);

									 DataRow dr = triggers.NewRow();
									 dr["Name"] = trigger.Name;

									 //dr["Schema"] = trigger.;
									 dr["Description"] = trigger.ExtendedProperties["MS_Description"] != null
															 ? trigger.ExtendedProperties["MS_Description"].Value.ToString()
															 : string.Empty;
									 dr["TableName"] = table.Name;
									 dr["InsteadOf"] = trigger.InsteadOf;
									 dr["IsEnabled"] = trigger.IsEnabled;
									 dr["CreateDate"] = trigger.CreateDate;
									 dr["DateLastModified"] = trigger.DateLastModified;
									 dr["Insert"] = trigger.Insert;
									 dr["Update"] = trigger.Update;
									 dr["Delete"] = trigger.Delete;

									 for (int i = 0; i < scriptArray.Length; i++)

										  //scriptArray[i] = Utility.SplitString(scriptArray[i], 150);
										  scriptArray[i] = scriptArray[i].Replace("@level0type", "\n\t@level0type");

									 dr["Script"] = string.Join(Environment.NewLine, scriptArray);

									 triggers.Rows.Add(dr);
								}
						  }
					 }
				}

				/// <summary>
				/// Writes the triggers.
				/// </summary>
				public static void WriteTriggers()
				{
					 WriteTriggerList();
					 WriteTriggerDetails();
				}

				/// <summary>
				/// Creates the triggers schema.
				/// </summary>
				private static void CreateTriggersSchema()
				{
					 triggers.Columns.Clear();

					 triggers.Columns.Add(new DataColumn("Name", typeof(string)));

					 //triggers.Columns.Add(new DataColumn("Schema", typeof(string)));
					 triggers.Columns.Add(new DataColumn("Description", typeof(string)));
					 triggers.Columns.Add(new DataColumn("TableName", typeof(string)));
					 triggers.Columns.Add(new DataColumn("InsteadOf", typeof(string)));
					 triggers.Columns.Add(new DataColumn("IsEnabled", typeof(string)));
					 triggers.Columns.Add(new DataColumn("Script", typeof(string)));
					 triggers.Columns.Add(new DataColumn("CreateDate", typeof(string)));
					 triggers.Columns.Add(new DataColumn("DateLastModified", typeof(string)));
					 triggers.Columns.Add(new DataColumn("Insert", typeof(string)));
					 triggers.Columns.Add(new DataColumn("Update", typeof(string)));
					 triggers.Columns.Add(new DataColumn("Delete", typeof(string)));
				}

				/// <summary>
				/// Writes the trigger details.
				/// </summary>
				private static void WriteTriggerDetails()
				{
					 string xmlfile = string.Empty;
					 string xslFile = string.Empty;
					 string htmFile = string.Empty;

					 var ds = new DataSet();

					 #region "Define DataTable for TableProperties"

					 var tableProperties = new DataTable("TableProperties");
					 tableProperties.Columns.Add("NAME", typeof(String));
					 tableProperties.Columns.Add("VALUE", typeof(String));
					 ds.Tables.Add(tableProperties);

					 #endregion "Define DataTable for TableProperties"

					 #region "Define DataTable for Type"

					 var tableType = new DataTable("TableType");
					 tableType.Columns.Add("InsteadOf", typeof(String));
					 tableType.Columns.Add("Insert", typeof(String));
					 tableType.Columns.Add("Update", typeof(String));
					 tableType.Columns.Add("Delete", typeof(String));
					 ds.Tables.Add(tableType);

					 #endregion "Define DataTable for Type"

					 #region "Define DataTable for SQL"

					 var tableSQL = new DataTable("SQL");
					 tableSQL.Columns.Add("CreationScript", typeof(String));
					 ds.Tables.Add(tableSQL);

					 #endregion "Define DataTable for SQL"

					 foreach (DataRow row in triggers.Rows)
					 {
						  string tableName = row["Name"].ToString();
						  xmlfile = tableName + ".xml";
						  xslFile = "TriggerDetails.xsl";
						  htmFile = tableName + ".htm";

						  #region "Fill TableProperties"

						  DataRow dr = tableProperties.NewRow();
						  dr["NAME"] = "Description";
						  dr["VALUE"] = row["Description"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Name";
						  dr["VALUE"] = tableName;
						  tableProperties.Rows.Add(dr);

						  //dr = tableProperties.NewRow();
						  //dr["NAME"] = "Schema";
						  //dr["VALUE"] = row["Schema"].ToString();
						  //tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "CreateDate";
						  dr["VALUE"] = row["CreateDate"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "IsEnabled";
						  dr["VALUE"] = row["IsEnabled"].ToString();
						  tableProperties.Rows.Add(dr);

						  #endregion "Fill TableProperties"

						  #region "Fill Columns"

						  DataRow[] colRows = triggers.Select("Name = '" + row["Name"] + "'");
						  foreach (DataRow tempDr in colRows)
						  {
								DataRow type = tableType.NewRow();
								type["InsteadOf"] = tempDr["InsteadOf"];
								type["Insert"] = tempDr["Insert"];
								type["Update"] = tempDr["Update"];
								type["Delete"] = tempDr["Delete"];
								tableType.Rows.Add(type);
						  }

						  #endregion "Fill Columns"

						  #region "SQL"

						  DataRow[] sqlRows = (triggers.Select("Name = '" + row["Name"] + "'"));
						  foreach (DataRow tempDr in sqlRows)
						  {
								DataRow sql = tableSQL.NewRow();
								sql["CreationScript"] = tempDr["Script"];
								tableSQL.Rows.Add(sql);
						  }

						  #endregion "SQL"

						  File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
						  var ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
						  ht.TableTransformer();

						  tableProperties.Clear();
						  tableType.Clear();
						  tableSQL.Clear();
					 }
				}

				/// <summary>
				/// Writes the trigger list.
				/// </summary>
				private static void WriteTriggerList()
				{
					 string xmlfile = "TriggerList.xml";
					 string xslFile = "TriggerList.xsl";
					 string htmFile = "TriggerList.htm";

					 var ds = new DataSet("TriggerList");
					 ds.Tables.Add(triggers.Copy());

					 File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());

					 var ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
					 ht.TableTransformer();
				}
		  }

		  /// <summary>
		  ///
		  /// </summary>
		  public class UserDefinedDataTypesHelper
		  {
				/// <summary>
				/// The user defined data types
				/// </summary>
				private static readonly DataTable userDefinedDataTypes = new DataTable("UserDefinedDataTypes");

				/// <summary>
				/// Gets the user defined data types.
				/// </summary>
				/// <value>
				/// The user defined data types.
				/// </value>
				public static DataTable UserDefinedDataTypes
				{
					 get { return userDefinedDataTypes; }
				}

				/// <summary>
				/// Gets the user defined data types.
				/// </summary>
				/// <param name="database">The database.</param>
				public static void GetUserDefinedDataTypes(string database)
				{
					 var serverConnection = new ServerConnection(Utility.DBConnection);
					 var server = new Server(serverConnection);
					 Database db = server.Databases[database];

					 var so = new ScriptingOptions();
					 so.ChangeTracking = true;
					 so.ClusteredIndexes = true;
					 so.ExtendedProperties = true;

					 CreateUserDefinedDataTypesSchema();
					 userDefinedDataTypes.Clear();

					 foreach (UserDefinedDataType uddt in db.UserDefinedDataTypes)
					 {
						  StringCollection script = uddt.Script(so);
						  var scriptArray = new string[script.Count];
						  script.CopyTo(scriptArray, 0);

						  DataRow dr = userDefinedDataTypes.NewRow();
						  dr["Name"] = uddt.Name;
						  dr["Schema"] = uddt.Schema;
						  dr["Parent"] = uddt.Parent;
						  dr["Description"] = ""; // uddt.ExtendedProperties["MS_Description"].Value.ToString();
						  dr["SystemType"] = uddt.SystemType;
						  dr["Nullable"] = uddt.Nullable;
						  dr["Rule"] = uddt.Rule;
						  dr["Default"] = uddt.Default;
						  dr["AllowIdentity"] = uddt.AllowIdentity;
						  dr["MaxLength"] = uddt.MaxLength;
						  dr["VariableLength"] = uddt.VariableLength;

						  for (int i = 0; i < scriptArray.Length; i++)

								//scriptArray[i] = Utility.SplitString(scriptArray[i], 150);
								scriptArray[i] = scriptArray[i].Replace("@level0type", "\n\t@level0type");

						  dr["Script"] = string.Join(Environment.NewLine, scriptArray);

						  userDefinedDataTypes.Rows.Add(dr);
					 }
				}

				/// <summary>
				/// Writes the user defined data types.
				/// </summary>
				public static void WriteUserDefinedDataTypes()
				{
					 WriteUserDefinedDataTypesList();
					 WriteUserDefinedDataTypesDetails();
				}

				/// <summary>
				/// Creates the user defined data types schema.
				/// </summary>
				private static void CreateUserDefinedDataTypesSchema()
				{
					 userDefinedDataTypes.Columns.Clear();

					 userDefinedDataTypes.Columns.Add(new DataColumn("Name", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("Schema", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("Parent", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("Description", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("SystemType", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("Nullable", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("Rule", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("Default", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("AllowIdentity", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("MaxLength", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("VariableLength", typeof(string)));
					 userDefinedDataTypes.Columns.Add(new DataColumn("Script", typeof(string)));
				}

				/// <summary>
				/// Writes the user defined data types details.
				/// </summary>
				private static void WriteUserDefinedDataTypesDetails()
				{
					 string xmlfile = string.Empty;
					 string xslFile = string.Empty;
					 string htmFile = string.Empty;

					 var ds = new DataSet();

					 #region "Define DataTable for TableProperties"

					 var tableProperties = new DataTable("TableProperties");
					 tableProperties.Columns.Add("NAME", typeof(String));
					 tableProperties.Columns.Add("VALUE", typeof(String));
					 ds.Tables.Add(tableProperties);

					 #endregion "Define DataTable for TableProperties"

					 #region "Define DataTable for Columns Defined on"

					 var columnsDefinedOn = new DataTable("ColumnsDefinedOn");
					 columnsDefinedOn.Columns.Add("Name", typeof(String));
					 columnsDefinedOn.Columns.Add("Parent", typeof(String));
					 columnsDefinedOn.Columns.Add("Description", typeof(String));
					 ds.Tables.Add(columnsDefinedOn);

					 #endregion "Define DataTable for Columns Defined on"

					 #region "Define DataTable for SQL"

					 var tableSQL = new DataTable("SQL");
					 tableSQL.Columns.Add("CreationScript", typeof(String));
					 ds.Tables.Add(tableSQL);

					 #endregion "Define DataTable for SQL"

					 foreach (DataRow row in userDefinedDataTypes.Rows)
					 {
						  string tableName = row["Name"].ToString();
						  xmlfile = tableName + ".xml";
						  xslFile = "UserDefinedDataTypeDetails.xsl";
						  htmFile = tableName + ".htm";

						  #region "Fill TableProperties"

						  DataRow dr = tableProperties.NewRow();
						  dr["NAME"] = "Description";
						  dr["VALUE"] = row["Description"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Name";
						  dr["VALUE"] = tableName;
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Schema";
						  dr["VALUE"] = row["Schema"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "SystemType";
						  dr["VALUE"] = row["SystemType"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Nullable";
						  dr["VALUE"] = row["Nullable"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Rule";
						  dr["VALUE"] = row["Rule"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Default";
						  dr["VALUE"] = row["Default"].ToString();
						  tableProperties.Rows.Add(dr);

						  #endregion "Fill TableProperties"

						  #region "Fill Columns"

						  DataRow[] colRows = userDefinedDataTypes.Select("Name = '" + row["Name"] + "'");
						  foreach (DataRow tempDr in colRows)
						  {
								DataRow type = columnsDefinedOn.NewRow();
								type["Name"] = tempDr["Name"];
								type["Parent"] = tempDr["Parent"];
								type["Description"] = tempDr["Description"];
								columnsDefinedOn.Rows.Add(type);
						  }

						  #endregion "Fill Columns"

						  #region "SQL"

						  DataRow[] sqlRows = (userDefinedDataTypes.Select("Name = '" + row["Name"] + "'"));
						  foreach (DataRow tempDr in sqlRows)
						  {
								DataRow sql = tableSQL.NewRow();
								sql["CreationScript"] = tempDr["Script"];
								tableSQL.Rows.Add(sql);
						  }

						  #endregion "SQL"

						  File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
						  var ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
						  ht.TableTransformer();

						  tableProperties.Clear();
						  columnsDefinedOn.Clear();
						  tableSQL.Clear();
					 }
				}

				/// <summary>
				/// Writes the user defined data types list.
				/// </summary>
				private static void WriteUserDefinedDataTypesList()
				{
					 string xmlfile = "UserDefinedDataTypeList.xml";
					 string xslFile = "UserDefinedDataTypeList.xsl";
					 string htmFile = "UserDefinedDataTypeList.htm";

					 var ds = new DataSet("UserDefinedDataTypeList");
					 ds.Tables.Add(userDefinedDataTypes.Copy());

					 File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());

					 var ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
					 ht.TableTransformer();
				}
		  }
	 }
}